#coding:utf-8
#!/usr/bin/python


"""
    cd /d D:\python2
    
    d:\python2.6.6\python.exe  d:\python2.6.6\dev_test\db_oracle.py

"""
import os,  string,time
import sys
reload(sys)  #方法真神奇
sys.setdefaultencoding('gbk')

try:
    #依赖于  DLL load failed: 找不到指定的模块。 
    #instantclient-basic-win32-10.2.0.4.zip (instant client) //解压到目录 并且设置到系统环境 变量里
    import cx_Oracle    

except Exception, e:
    print e
    print __file__
    sys.exit()



"""
cx_Oracle.Connection([user, password, dsn, mode, handle, pool, threaded, twophase, events, cclass, purity, newpassword])
cx_Oracle.makedsn(host, port, sid)

"""
#"""
#help(cx_Oracle.Connection)
#print dir(cx_Oracle.connect)
#help(cx_Oracle.connect)
# 连接本地
#con = cx_Oracle.connect( u"sys/woshiug@61.164.81.188 as sysdba") 
#      cx_Oracle.connect("hr/hrpwd@//localhost:1521/XE")
#exit

#"""

#"""
myUser = u"sys"
myPass = u"woshiug"
myIP = "61.164.81.188" 
myPort = 1521
mySID = 'XE'
#"""

"""
myUser = u"sys"
myPass = u"jk"
mySID = 'ora2'
myIP = "192.168.0.194" 
myPort = 1521
"""

"""
myDsn = cx_Oracle.makedsn(myIP, myPort,mySID)  
oracle = cx_Oracle.connect(myUser,myPass, myDsn,cx_Oracle.SYSDBA)
#密码错误 不会中断程序 只会抛出异常
print __file__ ,':the end'

exit()
"""

def put_err(arg_e,ct=1,):
    arg_type = type(arg_e)
    if( arg_type == str or arg_type == unicode ):
        print arg_e
    if(  arg_type == dict ):
        print arg_e['msg']
    if( not(ct ==1 ) ):
        exit()

class db_cls():
    
    def __init__(self): 
        u'构建 不做任何处理'
        pass

    def __del__(self):
        u"析构 关闭连接"
        if (self.init_state['state']  != 0): #未初始化
            pass
        #if self.con:
        self.con.close()
    
    def do_init(self):
        u'手工初始化'
        # 未初始化就出错
        try:
            self.init_state  is null
        except Exception, e:
            return self.__do_initx()
        return self.init_state
        
    def __do_initx(self):
        u'初始化'
        #成功0 失败!=0
        self.init_state = {'error':'not init','state':0}
        try:
            #连接比较特殊
            #raise Exception('spam', 'eggs')
            myDsn = cx_Oracle.makedsn(myIP, myPort,mySID)  
            self.con = cx_Oracle.connect(myUser,myPass, myDsn,cx_Oracle.SYSDBA)
            self.init_state['error']=''
            #raise NameError, u'这里有错误'
        except Exception, e:  
            u"""错误 是中文的 """
            self.init_state['error']=e
            self.init_state['state']=1
            print self.init_state['error']
            return  self.init_state
        #创建游标
        self.cursor = self.con.cursor()
        #初始化 状态结果
        return self.init_state
    def get_cur(self):
        u""" 返回 一个测试用 游标
            return obj/dict
            
        """
        if (self.init_state['state']  != 0): 
            return self.init_state

        return self.con.cursor()

    def call(self):
        u""" 存储过程 """
        # 可以获取输出 和 返回值
        
        
    
    
    def do_sql(self,sql,sql_arg=''):
        u""" 执行 sql
            param str sql
            return bool/dict
        """
        #未初始化  
        if (self.init_state['state']  != 0): 
            return self.init_state
        cursor = self.con.cursor()
        state = cursor.execute(sql)
        #居然 插入要提交   //会提交事务吗？？？？
        self.con.commit()
        cursor.close ()
        return state

        
        
    def get_one(self,sql):
        u"""  只返回一条结果
            param str  sql
            return dict 
        """
        if (self.init_state['state']  != 0): #未初始化
            return self.init_state

        #print cursor
        cursor = self.con.cursor()
        state = cursor.execute (sql)
        records = cursor.fetchone()

        if records is None:
            print 'sql error'
            return {}
            
        field = [i[0] for i in cursor.description]
        cursor.close ()

        records2 = dict(zip(field,records))
        return records2
    def get_all(self,sql):
        u""" 所有行
            param str sql
            return list
        """
        if (self.init_state['state']  != 0): #未初始化
            return [self.init_state]
 
        cursor = self.con.cursor()
        try:
            state = cursor.execute (sql)
        except Exception, e:
            put_err(u'sql 错误   '+str(e)+'\n'+sql,0)
        records = cursor.fetchall()
        self.rowcount = cursor.rowcount
        field = [i[0] for i in cursor.description]
        cursor.close()
        
        

        records2 = [dict(zip(field,i)) for i in records]
        return records2

def test_ddl(db):
    sql = u"""
    drop trigger sys.demo_bt
"""
    #print db.do_sql(sql)
    #return 
    sql = u"""
CREATE TABLE HR.demo(
    id Number NOT NULL PRIMARY KEY,
    name VARCHAR2(64)
)
"""
    print db.do_sql(sql)
    sql = u"""
CREATE SEQUENCE HR.demo_seq 
    INCREMENT BY 1 -- 每次加几个
    START WITH 1 -- 从1开始计数
    NOMAXVALUE -- 不设置最大值
    NOCYCLE -- 一直累加，不循环
    NOCACHE -- 不建缓冲区
"""
    print db.do_sql(sql)
    sql = u"""
CREATE TRIGGER HR.demo_bt
    BEFORE INSERT ON HR.demo
    for each row
    begin
     select HR.demo_seq.nextval into  :new.id from sys.dual;
    end;
"""

    print db.do_sql(sql)
#######################################

def test_sql(db):
    """ 测试 """
    sql = u"""
insert into "HR"."demo"
(name)
values
('aaa')
"""
    print db.do_sql(sql)

    sql = u"""
select ty.* from (
    select rownum r, tx.* from (
        select * from "HR"."demo"  order by id desc
    ) tx where rownum<= 100
) ty
where ty.r >= 1 
""" 
    ret_all = db.get_all(sql)
    print len(ret_all)
#######################################
def call(db):
    u"""
    测试  过程
    """
    cur = db.get_cur()
    #开始调用 (返回值)
    #print dir(cx_Oracle)
    #exit()
    
    print '1--',cur.callproc(u"dbms_output.enable")

    # 获取输出  
    arg_cur = cur.var(cx_Oracle.CURSOR)
    call_ret = cur.callproc(u"pro_py_cur", [arg_cur])  
    DbmsOutputGetLine(cur)  # print 打印 返回
    vv = arg_cur.getvalue()
    print vv.fetchall()


    exit()
    
def DbmsOutputGetLine(cur):
    """
    Retrieving dbms_output result line by line
    with dbms_output.get_line method.
    """
    line = cur.var(cx_Oracle.STRING)
    status = cur.var(cx_Oracle.NUMBER)
    while True:
        cur.callproc(u"dbms_output.get_line",(line,status))
        if status.getvalue() != 0:
            break
        else:
            print line.getvalue()  
              
"""
    arg_str =  cur.var(cx_Oracle.STRING)
    call_ret = cur.callproc(u"pro_py_str", [1,arg_str])  
    print arg_str.getvalue()
"""    
"""
    arg_cur = cur.var(cx_Oracle.CURSOR)
    call_ret = cur.callproc(u"pro_py_cur", [arg_cur])  
    vv = arg_cur.getvalue()
    print vv.fetchall()

"""
if __name__ == '__main__':
    db = db_cls()
    db.do_init()

    call(db)
    #test_ddl(db)
    #test_sql(db)

    print __file__ ,':the end'
    sys.exit()



